package cc.user;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import oracle.to.mysql.utils.SqlUtils;

/**
 * to data 
 *
 */
public class UserInsertE {
	
	private static Logger log = Logger.getLogger("");

	public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException {
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Class.forName("com.mysql.jdbc.Driver");
		Connection connOracle = DriverManager.getConnection(SqlUtils.ORACLE_URL, SqlUtils.ORACLE_USER, SqlUtils.ORACLE_PWD);
		Connection connMysql = DriverManager.getConnection(SqlUtils.getMySqlUrl());
		Statement stmtOracle = connOracle.createStatement();
		long time = System.currentTimeMillis();
		stmtOracle = connOracle.createStatement();
		ResultSet rs = stmtOracle.executeQuery("select USERID, ACCOUNT, PASSWORD, USER_NAME, MAIL, PHONE, QUESTION,"
				+ " ANSWER, REGISTER_TIME, LOGIN_TIME, LOGIN_NUM, IS_LOCKED, LOCK_USER_ID, LOCK_USER_NAME, "
				+ "LOCK_TIME, LOCK_REASON, ISVALID_MAIL, VALID_MAIL_TIME,  ISVALID_PHONE, VALID_PHONE_TIME, "
				+ " AAE014 ,ACB200 from sys_user_E "
				+ "where acb200 is not null and acb200 in("
				+ "select acb200 from cb20_cc where aab001 in(select aab001 from ab01_cc)"
				+ ")  order by acb200");
		ResultSetMetaData mt = rs.getMetaData();
		int cols = mt.getColumnCount()-2;
		String insert = "INSERT INTO `"+SqlUtils.MYSQL_DB+"`.`sys_user_account` (`USER_ID`, `ACCOUNT`, `PASSWORD`, `USER_NAME`,"
				+ " `MAIL`, `PHONE`, `QUESTION`, `ANSWER`, `REGISTER_TIME`, `LOGIN_TIME`, `LOGIN_NUM`, `IS_LOCKED`,"
				+ " `LOCK_USER_ID`, `LOCK_USER_NAME`, `LOCK_TIME`, `LOCK_REASON`, `ISVALID_MAIL`, `VALID_MAIL_TIME`,"
				+ "  `ISVALID_PHONE`, `VALID_PHONE_TIME`, `AAE014`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, ?,?,?,?,?,?,?,?,?) ";
		System.out.println(insert);
		while (rs.next()) {
			PreparedStatement stmt2 = connMysql.prepareStatement(insert);
			for (int c = 1; c <= cols; c++) {
				Object o = rs.getObject(c);
				if (o != null) {
					if (o instanceof oracle.sql.TIMESTAMP) {
						oracle.sql.TIMESTAMP ts = (oracle.sql.TIMESTAMP) o;
						o = new java.sql.Timestamp(ts.timestampValue().getTime());
					} else if (o instanceof java.util.Date) {
						o = new java.sql.Timestamp(((java.util.Date) o).getTime());
					} else if (o instanceof java.sql.Clob) {
						oracle.sql.CLOB clob = (oracle.sql.CLOB) o;
						Reader red = clob.getCharacterStream();
						char[] crs = new char[10240];
						String tmp = "";
						int len;
						while ((len = red.read(crs)) != -1) {
							tmp += new String(crs, 0, len);
						}
						red.close();
						o = new String(tmp);
					}
				}
				stmt2.setObject(c, o);
			}
			stmt2.setObject(cols+1, "财务系统导入企业");
			String userid = rs.getString("USERID");
			String acb200 = rs.getString("ACB200");
			Object ctime = rs.getTimestamp("REGISTER_TIME");
			String insertExt = "INSERT INTO `"+SqlUtils.MYSQL_DB+"`.`sys_user_account_ext`"
					+ " (`USER_ID`, `C_TIME`, `TYPE`, `BUSINESSID`, `BUSINESSNAME`, `ID`)"
					+ " VALUES (?, ?, 'E0', ?, '就业职介系统财务导入', ?)";
			PreparedStatement stmt3 = connMysql.prepareStatement(insertExt);
			stmt3.setString(1, userid);
			stmt3.setObject(2, ctime);
			stmt3.setString(3, acb200);
			stmt3.setString(4, userid);
			try {
				stmt2.execute();
				stmt3.execute();
				log.info(stmt2.toString());
			} catch (Exception e) {
				log.error("插入异常："+e.getMessage());
			}
			stmt2.close();
		}
		log.info("用时：" + (System.currentTimeMillis() - time) + "ms");
		rs.close();
		stmtOracle.close();
		stmtOracle.close();
		connMysql.close();
	}

}